--- dkr: separate the access key check from the main query, so that the access key check does not induce the optimizer an unwanted approach
/*
Given a document tree path, starting with the root, returns the RootHierarchyKey, HierarchyKey and DocumentKey of the
end node as out parameters. Security IS checked. The parts of the path must be separated by the forward slash (/).
NOTE: The documentKey returned will be that of the latest version
*/
CREATE PROC [dbo].[asi_DocumentKeysByPath]
   @documentPath nvarchar(2000),
   @organizationKey uniqueidentifier,
   @userKey uniqueidentifier,
   @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- if this is empty, we assume the user is not logged in
   @ignoreLicensing bit = 0,
   @rootHierarchyKey uniqueidentifier OUT,
   @hierarchyKey uniqueidentifier OUT,
   @documentVersionKey uniqueidentifier OUT,
   @documentKey uniqueidentifier OUT
AS
BEGIN
   DECLARE
      @parent uniqueidentifier,
      @relatedDocumentVersionKey uniqueidentifier,
      @relatedDocumentKey uniqueidentifier,
      @relatedHierarchyKey uniqueidentifier,
      @documentRootName nvarchar(100),
      @documentName nvarchar(100),
      @ptr int,
      @documentPathWorking nvarchar(2000)
      
   SET @ptr = CHARINDEX(N'/', @documentPath)
   IF @ptr > 0
   BEGIN
      --First segment contains the root name
      SET @documentRootName = SUBSTRING(@documentPath, 1, @ptr - 1)
      SET @documentPath = SUBSTRING(@documentPath, @ptr + 1, LEN(@documentPath) - @ptr)
   END
   ELSE
   BEGIN
        -- See if it's the name of a root; if not, default to $
        IF EXISTS (SELECT 1 FROM HierarchyRoot WHERE HierarchyRootName = @documentPath)
        BEGIN
              SET @documentRootName = @documentPath
              SET @documentPath = ''
        END
        ELSE
      BEGIN
              --No root specified so use absolute root name
              SET @documentRootName = N'$'
              IF @documentPath = N'$'
                   SET @documentPath = ''
        END
   END
   -- check to make sure the root exists and the user has rights to it
   -- Updated to handle multiple roots with the same name
   DECLARE @HierarchyRoots TABLE (
       RootHierarchyKey uniqueidentifier,
       HierarchyKey uniqueidentifier,
       UniformKey uniqueidentifier,
       DocumentKey uniqueidentifier,
       Checked bit
   )
   INSERT INTO @HierarchyRoots
   SELECT a.RootHierarchyKey,
          b.HierarchyKey,
          b.UniformKey,
          c.DocumentKey,
          0
     FROM HierarchyRoot a inner join Hierarchy b on a.RootHierarchyKey = b.HierarchyKey
          left join DocumentMain c ON b.UniformKey = c.DocumentVersionKey AND c.DocumentStatusCode IN (40,60)
    WHERE a.HierarchyRootName = @documentRootName
      AND a.OrganizationKey = @organizationKey
        AND (@rootHierarchyKey IS NULL OR @rootHierarchyKey = '00000000-0000-0000-0000-000000000000' OR a.RootHierarchyKey = @rootHierarchyKey)
      AND EXISTS(
          SELECT 1
            FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
           WHERE AccessItem.AccessKey = a.AccessKey
             AND UserToken.UserKey = @userKey
             AND (AccessItem.Permission&3)>0)

   WHILE @hierarchyKey IS NULL AND @documentVersionKey IS NULL AND @documentKey IS NULL AND EXISTS (SELECT 1 FROM @HierarchyRoots WHERE Checked = 0)
   BEGIN
       SET @documentPathWorking = @documentPath

         SELECT @rootHierarchyKey = RootHierarchyKey,
                    @hierarchyKey = HierarchyKey,
                    @documentVersionKey = UniformKey,
                    @documentKey = DocumentKey
             FROM @HierarchyRoots
            WHERE Checked = 0

         -- if we can get to the root, lets go inside the path
         IF @rootHierarchyKey IS NOT NULL
         BEGIN
          UPDATE @HierarchyRoots SET Checked = 1 WHERE RootHierarchyKey = @rootHierarchyKey

              SET @hierarchyKey = @rootHierarchyKey
              SET @parent = @rootHierarchyKey
              -- loop thru the path components
              WHILE LEN(@documentPathWorking) > 0
              BEGIN
                   -- separate the next part of the path from the rest
                   SET @ptr = CHARINDEX(N'/', @documentPathWorking)
                   IF @ptr > 0
                   BEGIN
                        SET @documentName = SUBSTRING(@documentPathWorking, 1, @ptr - 1)
                        SET @documentPathWorking = SUBSTRING(@documentPathWorking, @ptr + 1, LEN(@documentPathWorking) - @ptr)
                   END
                   ELSE
                   BEGIN
                        SET @documentName = @documentPathWorking
                        SET @documentPathWorking = N''
                   END

                   -- if there is still path parts left, documentName contains a folder name. Get its HierarchyKey
                   IF LEN(@documentName) > 0
                   BEGIN
                        SET @documentKey = null
                        SET @documentVersionKey = null
                        SET @hierarchyKey = null

                        DECLARE @t1 TABLE (
                              ParentHierarchyKey uniqueidentifier,
                              HierarchyKey uniqueidentifier,
                              DocumentVersionKey uniqueidentifier,
                              DocumentKey uniqueidentifier,
                              RelatedDocumentVersionKey uniqueidentifier,
                              AccessKey uniqueidentifier,
                              CreatedOn datetime
                        )
                        DELETE FROM @t1
                        INSERT INTO @t1 (ParentHierarchyKey, HierarchyKey, DocumentVersionKey, DocumentKey, RelatedDocumentVersionKey, AccessKey, CreatedOn)
                        SELECT
                                 Hierarchy.ParentHierarchyKey,
                                 Hierarchy.HierarchyKey,
                                 DocumentMain.DocumentVersionKey,
                                 DocumentMain.DocumentKey,
                                 DocumentMain.RelatedDocumentVersionKey,
                                 DocumentMain.AccessKey,
                                 DocumentMain.CreatedOn
                          FROM Hierarchy INNER JOIN DocumentMain ON Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
                         WHERE Hierarchy.RootHierarchyKey = @rootHierarchyKey
                           AND Hierarchy.ParentHierarchyKey = @parent
                           AND DocumentMain.DocumentName = @documentName

                        SELECT TOP 1 @parent = a.ParentHierarchyKey,
                              @hierarchyKey = a.HierarchyKey,
                              @documentVersionKey = a.DocumentVersionKey,
                              @documentKey = a.DocumentKey,
                              @relatedDocumentVersionKey = a.RelatedDocumentVersionKey
                        FROM @t1 AS a
                           WHERE EXISTS(
                                 SELECT 1
                                     FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
                                    WHERE AccessItem.AccessKey = a.AccessKey
                                      AND UserToken.UserKey = @userKey
                                      AND (AccessItem.Permission&3)>0)
                              AND (@ignoreLicensing = 1
                                 OR NOT EXISTS (SELECT 1 FROM UniformLicense WHERE UniformKey = a.DocumentVersionKey)
                                     OR EXISTS(
                                      SELECT 1
                                          FROM [dbo].[UniformLicense] INNER JOIN [dbo].[LicenseLegacyList] l ON UniformLicense.LicenseKey = l.LicenseLegacyKey
                                       WHERE UniformLicense.UniformKey = a.DocumentVersionKey))
                         ORDER BY a.CreatedOn DESC

                        IF @@ROWCOUNT = 0
                        BEGIN
                           SET @parent = NULL
                           SET @documentPathWorking = NULL
                        END
                        ELSE
                        BEGIN
                           IF (@hierarchyKey IS NOT NULL)
                                SET @parent = @hierarchyKey
                           -- get related document info if there
                           IF (@relatedDocumentVersionKey IS NOT NULL)
                           BEGIN
                                SELECT top 1 @relatedHierarchyKey = HierarchyKey, @relatedDocumentKey = DocumentMain.DocumentKey
                                    FROM Hierarchy INNER JOIN DocumentMain ON Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
                                 WHERE Hierarchy.RootHierarchyKey = @rootHierarchyKey
                                     AND DocumentMain.DocumentVersionKey = @relatedDocumentVersionKey
                                     AND EXISTS(
                                           SELECT 1
                                             FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
                                            WHERE AccessItem.AccessKey = DocumentMain.AccessKey
                                                AND UserToken.UserKey = @userKey
                                                AND (AccessItem.Permission&3)>0)
                                    AND (@ignoreLicensing = 1
                                       OR NOT EXISTS (SELECT 1 FROM UniformLicense WHERE UniformKey = DocumentMain.DocumentVersionKey)
                                           OR EXISTS(
                                            SELECT 1
                                                FROM [dbo].[UniformLicense] INNER JOIN [dbo].[LicenseLegacyList] l ON UniformLicense.LicenseKey = l.LicenseLegacyKey
                                             WHERE UniformLicense.UniformKey = DocumentMain.DocumentVersionKey))
                                 ORDER BY DocumentMain.CreatedOn DESC

                                IF @@ROWCOUNT = 1 AND @relatedHierarchyKey IS NOT NULL
                                BEGIN
                                     SET @parent = @relatedHierarchyKey
                                     SET @documentKey = @relatedDocumentKey
                                END
                           END
                        END
                   END
              END -- WHILE LEN(@documentPathWorking) > 0
         END -- IF @rootHierarchyKey IS NOT NULL
   END -- WHILE Checked = 0END
END

GO